package com.wanmait.travel.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.travel.util.DbHelper;
import com.wanmait.travel.vo.Comment;
import com.wanmait.travel.vo.Userinfo;

public class CommentDAO {
		private static CommentDAO commentDAO;
		private CommentDAO() {}
		public static CommentDAO getDAO() {
		if(commentDAO==null)
		{
			commentDAO=new CommentDAO();
		}
		return commentDAO;
		
		}
		public void insertAnding(int scenicid, int anding) {
			
			String sql="update scenic set anding=? where id=?" ;
			ArrayList params=new ArrayList();
			params.add(anding);
			params.add(scenicid);
			DbHelper.update(sql, params);
		}
		public void insert(Comment comment) {
			String sql="insert into scenic(userinfo_id,scenic_id,comment)value(?,?,?)";
			ArrayList params=new ArrayList();
			params.add(comment.getUserId());
			params.add(comment.getScenicId());
			params.add(comment.getComment());
			DbHelper.update(sql, params);
		}
		public void deleted(int id)
		{
			String sql="UPDATE comment set enable=0 WHERE id=?" ;
			ArrayList params=new ArrayList();
			params.add(id);
			DbHelper.update(sql, params);
			
		}
		public List<Comment>findByScenicId(int scenicid )
		{
			ArrayList<Comment> comments=new ArrayList<Comment>();
			String sql="select comment.*,userinfo.nickname" + 
							" from comment left join userinfo on comment.userinfo_id=userinfo.id " + 
							" where scenic_id=? and comment.enable=1 order by comment_date desc";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps=conn.prepareStatement(sql);
				ps.setInt(1, scenicid);
				rs=ps.executeQuery();
				while(rs.next())
				{
					Comment comment = new Comment();
					comment.setId(rs.getInt("id"));
					comment.setScenicId(rs.getInt("scenic_id"));
					comment.setUserId(rs.getInt("userinfo_id"));
					comment.setCommmentDate(rs.getTimestamp("comment_date"));
					comment.setEnable(rs.getBoolean("enable"));
					comment.setComment(rs.getString("comment"));
					
					Userinfo userinfo=new Userinfo();
					userinfo.setId(rs.getInt("id"));
					userinfo.setNickname(rs.getString("nickname"));
					comment.setUserinfo(userinfo);
					comments.add(comment);
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			
			return comments;
		}
		 
}
